package com.db.sqlite;

import java.util.ArrayList;

import com.db.NLP.LanguageProcessing;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHandler extends SQLiteOpenHelper {
	
	private static final String DB_NAME = "mrkentuckydb3";
	private static final int DB_VERSION = 1;
	private static final String CREATE_TABLE =
			"CREATE TABLE word(wordID INTEGER PRIMARY KEY AUTOINCREMENT, category TEXT, answer TEXT, keyword TEXT);";
	private static final String CREATE_SAVE_STATE_TABLE =
			"CREATE TABLE messages(category TEXT, comment TEXT, left TEXT);";
	
	private static final String WHO1 = "INSERT INTO word (category, answer, keyword) VALUES ('who', 'Hello there! My name is Mr. Kentucky! I am your KFC electronic receptionist and friend! :)', 'you')";
	private static final String WHAT1 = "INSERT INTO word (category, answer, keyword) VALUES ('what', 'Just dial the number (02)887-8888, young one.', 'number call dial KFC delivery hotline order')";
	private static final String WHEN1 = "INSERT INTO word (category, answer, keyword) VALUES ('when', '2012, by a group of awesome and goodlooking students. Seriously! Hehehe.', 'you created')";
	private static final String WHERE1 = "INSERT INTO word (category, answer, keyword) VALUES ('where', 'The nearest KFC stores are located specifically in University Mall @ 2507 A Taft Ave Malate, Manila and in the Harrison Plaza @ Fc Harrison St Cor Vito Cruz Malate, Manila', 'nearest DLSU KFC store')";
	
	public DBHandler(Context context, String name, CursorFactory factory,
			int version) {
		super(context, DB_NAME, null, DB_VERSION);
		// TODO Auto-generated constructor stub
	}
	
	public DBHandler(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(CREATE_TABLE);
		db.execSQL(WHO1);
		db.execSQL(WHAT1);
		db.execSQL(WHEN1);
		db.execSQL(WHERE1);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}
	
	public void initialize() {

		//-- WHO QUESTIONS (ID's 1 - 250)
		//Who are you?
		this.addWord("who", "Hello there! My name is Mr. Kentucky! I am your KFC electronic receptionist and friend! :)", "are you");
		
		
		//-- WHAT QUESTIONS (ID's 251-500)
		//What	number do I call for delivery?/What number shall I dial for KFC delivery?
		this.addWord("what", " Just dial the number (02)887-8888, young one.", "number call dial KFC delivery hotline order");
		
		//-- WHEN QUESTIONS (ID's 501-750)
		//When	were you created?
		this.addWord("when", "2012, by a group of awesome and goodlooking students. Seriously! Hehehe.", "you created");
		
		//-- WHERE QUESTIONS (ID's 751-1000)
		//Where	is the nearest KFC store in DLSU?/Lasalle
		this.addWord("where", "The nearest KFC stores are located specifically in University Mall @ 2507 A Taft Ave Malate, Manila and in the Harrison Plaza @ Fc Harrison St Cor Vito Cruz Malate, Manila", "nearest DLSU Lasalle KFC store");
		
	}
	
	
	public void addWord(String category, String answer, String keyword) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("category", category);
		cv.put("answer", answer);
		cv.put("keyword", keyword);
		
		db.insert("word", null, cv);
		
		db.close();
	}
	

	
	public String getAnswerBasedOnKeywordAndCategory(String kwordList, String category) {
		String result = "";
		while(kwordList.endsWith(" ")){
			kwordList = kwordList.substring(0, kwordList.length()-1);
		}
		String selectQuery = "select answer from word where category = '"+category+"' and " +
				"keyword like ('%"+kwordList+"%')";
		//String selectQuery = "Select answer from word";
		
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		Log.d("category ", category);
		Log.d("KEYWORDS ", "'"+kwordList+"'");
		if(cursor.moveToFirst()){
			do{
				result = cursor.getString(0);
				Log.d("ANSWER FROM DB", result);
				break;
			}while(cursor.moveToNext());		
			
		}
		
		cursor.close();
		db.close();
			
		return result;
	}
	
	public String getAnswerBasedOnKeywordAndCategory(ArrayList<String> kwordList, String category) {
		String result = "";
		//String keywords = "you created";
		String keywords = LanguageProcessing.convertArrayListToStringSeparatedBySpace(kwordList);
		keywords = keywords.substring(1,keywords.length()-2);
		Log.d("KEYWORDS ", keywords);
		String selectQuery = "select answer from word where category = '"+category+"' and " +
				"keyword in ('"+keywords+"')";
		//String selectQuery = "Select answer from word";
		
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		
		if(cursor.moveToFirst()){
			do{
				result = cursor.getString(0);
				Log.d("ANSWER FROM DB", result);
				break;
			}while(cursor.moveToNext());		
			
		}
		
		cursor.close();
		db.close();
			
		return result;
	}
}
